package com.pineapple.module.core.hitfertilizer.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import com.pineapple.module.core.hitfertilizer.domain.Hitfertilizer;
import com.pineapple.module.core.hitfertilizer.domain.HitfertilizerCriteria;

public class HitfertilizerDAO {

	protected int count(HitfertilizerCriteria criteria,Connection conn) throws Exception{
 		int totRecord =0;

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) AS TOT  ");
		sql.append("FROM ");
		sql.append("hitfertilizer h ");
		sql.append("Inner Join fertilizer ON fertilizer.fertilizer_id = h.hitfertilizer_fertilizer ");
		sql.append("Inner Join planting ON h.hitfertilizer_area = planting.planting_id ");
		sql.append("Inner Join area ON planting.area_id = area.area_id ");

		sql.append("WHERE 1=1 ");

		if(criteria.getHitfertilizerId()!=null && !criteria.getHitfertilizerId().equals("")){
			sql.append("AND h.hitfertilizer_Id='"+criteria.getHitfertilizerId()+"' ");
		}
		if(criteria.getHitfertilizerArea()!=null && !criteria.getHitfertilizerArea().equals("")){
			sql.append("AND h.hitfertilizer_area='"+criteria.getHitfertilizerArea()+"' ");
		}
		if(criteria.getHitfertilizerDateFrom()!=null && !criteria.getHitfertilizerDateFrom().equals("")){
			sql.append("AND h.hitfertilizer_date  >='"+dateForInsert(criteria.getHitfertilizerDateFrom())+"' ");
		}
		if(criteria.getHitfertilizerDateTo()!=null && !criteria.getHitfertilizerDateTo().equals("")){
			sql.append("AND h.hitfertilizer_date <='"+dateForInsert(criteria.getHitfertilizerDateTo())+"' ");
		}
		if(criteria.getHitfertilizerFertilizer()!=null && !criteria.getHitfertilizerFertilizer().equals("")){
			sql.append("AND h.hitfertilizer_fertilizer ='"+criteria.getHitfertilizerFertilizer()+"' ");
		}

		System.out.println(sql);
		try(Statement stmt = conn.createStatement()){
			ResultSet rst = stmt.executeQuery(sql.toString());
			if (rst.next()) {
				totRecord = rst.getInt("TOT");
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return totRecord;
	}



	protected List<Hitfertilizer> search(HitfertilizerCriteria criteria,Connection conn) throws Exception{
		List<Hitfertilizer> lstHitfertilizer = new ArrayList<Hitfertilizer>();
		Hitfertilizer hitfertilizer = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("f.fertilizer_name,");
		sql.append("h.hitfertilizer_Id,");
		sql.append("h.hitfertilizer_date,");
		sql.append("h.hitfertilizer_fertilizer,");
		//sql.append("h.hitfertilizer_area,");
		sql.append("h.hitfertilizer_value,");
		sql.append("planting.planting_status, ");
		sql.append("planting.area_id, ");
		sql.append("planting.planting_id, ");
		sql.append("a.area_name ");
		sql.append("FROM ");
		sql.append("hitfertilizer h ");
		sql.append("Inner Join fertilizer f ON f.fertilizer_id = h.hitfertilizer_fertilizer ");
		sql.append("Inner Join planting ON h.hitfertilizer_area = planting.planting_id ");
		sql.append("Inner Join area a ON planting.area_id = a.area_id ");
		sql.append("WHERE 1=1 ");


		if(criteria.getHitfertilizerId()!=null && !criteria.getHitfertilizerId().equals("")){
			sql.append("AND h.hitfertilizer_Id='"+criteria.getHitfertilizerId()+"' ");
		}
		if(criteria.getHitfertilizerArea()!=null && !criteria.getHitfertilizerArea().equals("")){
			sql.append("AND h.hitfertilizer_area='"+criteria.getHitfertilizerArea()+"' ");
		}
		if(criteria.getHitfertilizerDateFrom()!=null && !criteria.getHitfertilizerDateFrom().equals("")){
			sql.append("AND h.hitfertilizer_date  >='"+dateForInsert(criteria.getHitfertilizerDateFrom())+"' ");
		}
		if(criteria.getHitfertilizerDateTo()!=null && !criteria.getHitfertilizerDateTo().equals("")){
			sql.append("AND h.hitfertilizer_date <='"+dateForInsert(criteria.getHitfertilizerDateTo())+"' ");
		}
		if(criteria.getHitfertilizerFertilizer()!=null && !criteria.getHitfertilizerFertilizer().equals("")){
			sql.append("AND h.hitfertilizer_fertilizer ='"+criteria.getHitfertilizerFertilizer()+"' ");
		}

		System.out.println(sql.toString());

		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	hitfertilizer= new Hitfertilizer();
		        	hitfertilizer.setHitfertilizerFertiliName(rs.getString("fertilizer_name"));
		        	hitfertilizer.setHitfertilizerArea(rs.getString("area_name"));
		        	hitfertilizer.setHitfertilizerAreaId(rs.getString("planting_id"));
		         	hitfertilizer.setHitfertilizerId(rs.getString("hitfertilizer_Id"));
		        	hitfertilizer.setHitfertilizerValue(rs.getString("hitfertilizer_value"));
		        	hitfertilizer.setPlantingStatus(rs.getString("planting_status"));
		        	hitfertilizer.setHitfertilizerDate(dateForShow(rs.getDate("hitfertilizer_date",Calendar.getInstance(Locale.ENGLISH))));
		        	lstHitfertilizer.add(hitfertilizer);
		        }


		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstHitfertilizer;
	}

	protected Map<String,String> fertilizerCombo(Connection conn)throws Exception{
		Map<String,String> lstPineapple = new HashMap<String, String>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM fertilizer ");
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	lstPineapple.put(rs.getString("fertilizer_id"),rs.getString("fertilizer_name"));
		        }
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstPineapple;
	}




	protected void add(Connection conn, Hitfertilizer hitfertilizer)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO hitfertilizer (hitfertilizer_date,hitfertilizer_fertilizer" +
				",hitfertilizer_area,hitfertilizer_value ) ");
		sql.append("VALUES ( " );
		sql.append("'"+dateForInsert(hitfertilizer.getHitfertilizerDate())+"' ");
		sql.append(",'"+hitfertilizer.getHitfertilizerFertilizer()+"' ");
		sql.append(",'"+hitfertilizer.getHitfertilizerAreaId()+"' ");
		sql.append(",'"+hitfertilizer.getHitfertilizerValue()+"' ");
		sql.append(" )");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			 stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}


	protected void edit(Connection conn, Hitfertilizer hitfertilizer)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE hitfertilizer SET ");
		sql.append(" hitfertilizer_date='"+dateForInsert(hitfertilizer.getHitfertilizerDate())+"' ");
		sql.append(",hitfertilizer_fertilizer='"+hitfertilizer.getHitfertilizerFertilizer()+"' ");
		sql.append(",hitfertilizer_area='"+hitfertilizer.getHitfertilizerArea()+"' ");
		sql.append(",hitfertilizer_value='"+hitfertilizer.getHitfertilizerValue()+"' ");
		sql.append(" WHERE hitfertilizer_id="+hitfertilizer.getHitfertilizerId());

		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	protected void delete(Connection conn, String hitfertilizerIds)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM hitfertilizer  ");
		sql.append(" WHERE hitfertilizer_id IN ("+hitfertilizerIds+")");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

    protected int countStatus(Connection conn , String areaId) throws Exception{
		StringBuilder sql = new StringBuilder();
		int i = 0;
		sql.append("SELECT COUNT(*) AS tot FROM planting  ");
		sql.append("WHERE planting.planting_status = '2' or planting.planting_status = '5' ");
		sql.append("And area_id = '"+areaId+"' ");
		try(Statement stmt = conn.createStatement()){
			ResultSet rs = stmt.executeQuery(sql.toString());
			if(rs.next()) {
			   i = rs.getInt("tot");
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return i;
    }


    protected int countNumb(Connection conn , String areaId) throws Exception{
 		StringBuilder sql = new StringBuilder();
 		int i = 0;
 		sql.append("SELECT COUNT(hitfertilizer.hitfertilizer_area) AS tot ");
 		sql.append("FROM hitfertilizer ");
 		sql.append("WHERE hitfertilizer.hitfertilizer_area ='"+areaId+"' ");
 		System.out.println(sql.toString());
 		try(Statement stmt = conn.createStatement()){
 			ResultSet rs = stmt.executeQuery(sql.toString());
 			if(rs.next()) {
 			   i = rs.getInt("tot");
 			}
 		}catch(Exception ex){
 			ex.printStackTrace();
 			throw ex;
 		}
 		return i;
     }



	protected Hitfertilizer searchById(Connection conn,int id)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("hitfertilizer.hitfertilizer_Id, ");
		sql.append("hitfertilizer.hitfertilizer_date, ");
		sql.append("hitfertilizer.hitfertilizer_fertilizer, ");
		sql.append("hitfertilizer.hitfertilizer_area, ");
		sql.append("hitfertilizer.hitfertilizer_value, ");
		sql.append("planting.planting_status ");
		sql.append("FROM ");
		sql.append("hitfertilizer ");
		sql.append("Inner Join planting ON planting.planting_id = hitfertilizer.hitfertilizer_area ");
		sql.append("WHERE hitfertilizer_Id ="+id);
		Hitfertilizer hitfertilizer = null;
		try(Statement stmt = conn.createStatement()){
			ResultSet rs = stmt.executeQuery(sql.toString());
			hitfertilizer = new Hitfertilizer();
	        if(rs.next()) {
	        	hitfertilizer.setHitfertilizerId(rs.getString("hitfertilizer_id"));
	        	hitfertilizer.setHitfertilizerArea(rs.getString("hitfertilizer_area")+","+rs.getString("planting_status"));
	        	hitfertilizer.setHitfertilizerFertilizer(rs.getString("hitfertilizer_fertilizer"));
	        	hitfertilizer.setHitfertilizerDate(dateForShow(rs.getDate("hitfertilizer_date")));
	        	hitfertilizer.setHitfertilizerValue(rs.getString("hitfertilizer_value"));
	        	hitfertilizer.setPlantingStatus(rs.getString("planting_status"));
	        }
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return hitfertilizer;
	}


	protected Map<String,String> searchComboPlantAndArea(Connection conn) throws Exception{
		StringBuilder sql = new StringBuilder();
		Map<String,String> lstArea = new LinkedHashMap<String, String>();
		sql.append("SELECT a.area_name,p.planting_id,a.area_id,p.planting_status ");
		sql.append("FROM planting p INNER JOIN area a ON p.area_id = a.area_id ");
		sql.append("WHERE p.planting_status=1 or p.planting_status=4 or p.planting_status=2 or p.planting_status=5 ");
		try(Statement stmt = conn.createStatement()){
			ResultSet rs = stmt.executeQuery(sql.toString());
	        while(rs.next()) {
	        	lstArea.put(rs.getString("planting_id")+","+rs.getString("planting_status"), rs.getString("area_name"));
	        }
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstArea;
	}


	protected void updateStatus(Connection conn,String plantingId,int status)throws Exception{
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE  planting SET ");
		sql.append("planting_status='"+status+"' ");
		sql.append(" WHERE planting_id='"+plantingId+"' ");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}

	}
	public static String dateForShow(Date date){
		String returnValue = null;
		try {
			if(date != null){
				returnValue = convertToString(date,"dd/MM/yyyy");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}

	public static String convertToString(Date date, String format) {
		if (date != null) {
			SimpleDateFormat formatDate = new SimpleDateFormat(format.toString(),Locale.ENGLISH);

			StringBuilder fdate = new StringBuilder(formatDate.format(date));
			return fdate.toString();
		} else {
			return "";
		}
	}


	public static String dateForInsert(String date){
		String returnValue = null;
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("en", "EN"));
		try {
			if(date != null && !date.trim().equals("")){
				returnValue = convertToString(sdf.parse(date),"yyyy-MM-dd");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}
}
